(ns clojure-demo.trade
  (:use [clojure.java.io]))

;;;;;;;;;;;;;;;
(def db-spec {:classname "org.sqlite.JDBC"
              :subprotocol "sqlite"
              :subname "test.db"})
;= #'user/db

{:classname "com.mysql.jdbc.Driver"
 :subprotocol "mysql"
 :subname "//localhost:3306/databasename"
 :username "login"
 :password "password"}

{:datasource datasource-instance
 :username "login"
 :password "password"}

{:name "java:/comp/env/jdbc/postgres"
 :environment {}} ; optional JNDI parameters for initializing javax.naming.InitialContext

"mysql://login:password@localhost:3306/databasename"

(jdbc/with-connection db-spec)
;= nil

(jdbc/with-connection db-spec
  (jdbc/create-table :authors
    [:id "integer primary key"]
    [:first_name "varchar"]
    [:last_name "varchar"]))
;= (0)

(jdbc/with-connection db-spec
  (jdbc/insert-records :authors
    {:first_name "Chas" :last_name "Emerick"}
    {:first_name "Christophe" :last_name "Grand"}
    {:first_name "Brian" :last_name "Carper"}))
;= ({:last_insert_rowid() 1}
;= {:last_insert_rowid() 2}
;= {:last_insert_rowid() 3})

(jdbc/with-connection db-spec
  (jdbc/with-query-results res ["SELECT * FROM authors"]
    (doall res)))
;= ({:id 1, :first_name "Chas", :last_name "Emerick"}
;= {:id 2, :first_name "Christophe", :last_name "Grand"}
;= {:id 3, :first_name "Brian", :last_name "Carper"})

(jdbc/with-connection db-spec
  (jdbc/with-query-results res ["SELECT * FROM authors"]
    (doall (map #(str (:first_name %) " " (:last_name %)) res))))
;= ("Chas Emerick" "Christophe Grand" "Brian Carper")

;;
(jdbc/with-connection db-spec
  (jdbc/with-query-results res ["SELECT * FROM authors WHERE id = ?" 2]
    (doall res)))
;= ({:id 2, :first_name "Christophe", :last_name "Grand"})

(jdbc/with-connection db-spec
  (jdbc/with-query-results res ["SELECT * FROM authors"]
    res))
;= ({:id 1, :first_name "Chas", :last_name "Emerick"})

(defn fetch-results [db-spec query]
  (jdbc/with-connection db-spec
    (jdbc/with-query-results res query
      (doall res))))
;= #'user/fetch-results

(fetch-results db-spec ["SELECT * FROM authors"])
;= ({:id 1, :first_name "Chas", :last_name "Emerick"}
;= {:id 2, :first_name "Christophe", :last_name "Grand"}
;= {:id 3, :first_name "Brian", :last_name "Carper"})


;;
(jdbc/with-connection db-spec
  (jdbc/transaction
    (jdbc/delete-rows :authors ["id = ?" 1])
    (throw (Exception. "Abort transaction!"))))
;= ; Exception Abort transaction!

(fetch-results ["SELECT * FROM authors where id = ?" 1])
;= ({:id 1, :first_name "Chas", :last_name "Emerick"})

(jdbc/with-connection db-spec
  (.setTransactionIsolation (jdbc/connection)
    java.sql.Connection/TRANSACTION_SERIALIZABLE)
  (jdbc/transaction
    (jdbc/delete-rows :authors ["id = ?" 2])))

;;;;
(import 'com.mchange.v2.c3p0.ComboPooledDataSource)
; Feb 05, 2011 2:26:40 AM com.mchange.v2.log.MLog <clinit>
; INFO: MLog clients using java 1.4+ standard logging.
;= com.mchange.v2.c3p0.ComboPooledDataSource

(defn pooled-spec
  [{:keys [classname subprotocol subname username password] :as other-spec}]
  (let [cpds (doto (ComboPooledDataSource.)
               (.setDriverClass classname)
               (.setJdbcUrl (str "jdbc:" subprotocol ":" subname))
               (.setUser username)
               (.setPassword password))]
    {:datasource cpds}))

(def pooled-db (pooled-spec db-spec))
; Dec 27, 2011 8:49:28 AM com.mchange.v2.c3p0.C3P0Registry banner
; INFO: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
;= #'user/pooled-db

(fetch-results pooled-db ["SELECT * FROM authors"])
; Dec 27, 2011 8:56:40 AM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource

getPoolManager
; INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource
; [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, ...
;= ({:id 1, :first_name "Chas", :last_name "Emerick"}
;= {:id 2, :first_name "Christophe", :last_name "Grand"}
;= {:id 3, :first_name "Brian", :last_name "Carper"})

(fetch-results pooled-db ["SELECT * FROM authors"])
;= ({:id 1, :first_name "Chas", :last_name "Emerick"}
;= {:id 2, :first_name "Christophe", :last_name "Grand"}
;= {:id 3, :first_name "Brian", :last_name "Carper"})
